﻿Imports Seguros.My.Resources
Imports System.Transactions

Imports Seguros.caserDataSet
Imports System.Data.OleDb
Imports Seguros.caserDataSetTableAdapters

Module Manejo_BD


    Friend Function GuardarTablasTransaccion(ByVal oTablasTransaccion As List(Of DataTable)) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)

        Using tsTransaccion As New TransactionScope
            Try
                'Se abre una sola conexión (NO es necesario para la transaccion)
                dbConn.Open()

                For Each dtTabla As DataTable In oTablasTransaccion

                    If TypeOf dtTabla Is personasDataTable Then
                        Dim tapersonas As New personasTableAdapter

                        tapersonas.Connection = dbConn

                        tapersonas.Update(dtTabla)

                    ElseIf TypeOf dtTabla Is relacionesDataTable Then

                        Dim tarelaciones As New relacionesTableAdapter
                        tarelaciones.Connection = dbConn
                        For Each relacionesR As relacionesRow In dtTabla.GetChanges.Rows
                            If (relacionesR.RowState = DataRowState.Added) Then
                                If (dtTabla.Select("idPersona1 = " & relacionesR.IdPersona2 & " AND  idPersona2 = " & relacionesR.idPersona1).Length >= 0) Then
                                    dtTabla.Select("idPersona1 = " & relacionesR.IdPersona2 & " AND  idPersona2 = " & relacionesR.idPersona1)(0).Delete()
                                End If

                                Select Case relacionesR.Relacion
                                    Case "Padre"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Hijo")
                                    Case "Hijo"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Padre")
                                    Case "Tio"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Sobrino")
                                    Case "Sobrino"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Tio")
                                    Case "Primo"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Primo")
                                    Case "Abuelo"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Nieto")
                                    Case "Nieto"
                                        CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Abuelo")
                                End Select




                            End If



                        Next



                        tarelaciones.Update(dtTabla)



                    End If
                Next

                'Si todo ha ido bien hasta este punto puedo confirmar los cambios en todos los datatables
                For Each dtTabla As DataTable In oTablasTransaccion
                    dtTabla.AcceptChanges()
                Next

                'Como todo ha ido bien cierro la transaccion (Igual a commit)
                tsTransaccion.Complete()

            Catch ex As Exception
                'Capturo el mensaje de error
                sError = ex.ToString

                'Como ha habido una excepcion deshago los cambios en todos los datatables (OPCIONAL)
                For Each dtTabla As DataTable In oTablasTransaccion
                    dtTabla.RejectChanges()
                Next
            Finally
                'Cierro la conexión si llegó a abrirse
                If dbConn.State = ConnectionState.Open Then
                    dbConn.Close()
                End If
            End Try

        End Using


        Return sError
    End Function



    Friend Function CargarVistaRelaciones(ByVal dtTabla As DataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)

        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taVista As New VistaRelacionesTableAdapter

            taVista.Connection = dbConn

            taVista.Fill(dtTabla, id)
        Catch ex As OleDbException

            sError = ex.ToString

        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try

        Return sError
    End Function



    'Friend Function CargarVistaDocContratadas(ByVal dtTabla As DataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taVista As New VistaDocenciasContratadasTableAdapter

    '        taVista.Connection = dbConn

    '        taVista.Fill(dtTabla, id)
    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return sError
    'End Function

    'Friend Function CargarMatriculasAlumno(ByVal dtTabla As DataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taMatriculas As New matriculasTableAdapter

    '        taMatriculas.Connection = dbConn

    '        taMatriculas.MatriculasAlumno(dtTabla, id)
    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return sError
    'End Function


    'Friend Function CargarFacturasAlumno(ByVal dtTabla As DataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taFacturas As New facturasTableAdapter

    '        taFacturas.Connection = dbConn

    '        taFacturas.FacturasAlumno(dtTabla, id)
    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return sError
    'End Function



    'Friend Function CargarVistaDocProf(ByVal dtTabla As DataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taVista As New VistaDocenciaProfTableAdapter

    '        taVista.Connection = dbConn

    '        taVista.Fill(dtTabla, id)
    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return sError
    'End Function





    Friend Function CargarTabla(ByVal dtTabla As DataTable) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)

        Try
            'Se abre la conexion
            dbConn.Open()

            If TypeOf dtTabla Is personasDataTable Then
                Dim taPersonas As New personasTableAdapter

                taPersonas.Connection = dbConn

                taPersonas.Fill(dtTabla)


            ElseIf TypeOf dtTabla Is notificacionesDataTable Then
                Dim tanotificaciones As New notificacionesTableAdapter

                tanotificaciones.Connection = dbConn

                tanotificaciones.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is polizaDataTable Then
                Dim tapoliza As New polizaTableAdapter

                tapoliza.Connection = dbConn

                tapoliza.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is autoDataTable Then
                Dim taauto As New autoTableAdapter

                taauto.Connection = dbConn

                taauto.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is hogarDataTable Then
                Dim tahogar As New hogarTableAdapter

                tahogar.Connection = dbConn

                tahogar.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is pymecomercioDataTable Then
                Dim tapymecomercio As New pymecomercioTableAdapter

                tapymecomercio.Connection = dbConn

                tapymecomercio.Fill(dtTabla)


            ElseIf TypeOf dtTabla Is relacionesDataTable Then
                Dim taclientes As New relacionesTableAdapter

                taclientes.Connection = dbConn

                taclientes.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is contactoDataTable Then
                Dim taclientes As New contactoTableAdapter

                taclientes.Connection = dbConn

                taclientes.Fill(dtTabla)

            ElseIf TypeOf dtTabla Is loginDataTable Then
                Dim taclientes As New loginTableAdapter

                taclientes.Connection = dbConn

                taclientes.Fill(dtTabla)


            End If



        Catch ex As OleDbException
            sError = ex.ToString

        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try

        Return sError
    End Function


    Public Function CargarClienteById(ByVal dtPersonas As personasDataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taPersona As New personasTableAdapter
            taPersona.Connection = dbConn
            taPersona.BuscarPersona(dtPersonas, id)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function


    Public Function CargarPolizasByIdPersona(ByVal dtPolizas As polizaDataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taPolizas As New polizaTableAdapter
            taPolizas.Connection = dbConn
            taPolizas.FillByPersona(dtPolizas, id)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function

    Public Function CargarCuentasById(ByVal dtcuentas As cuentacorrienteDataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim tacuentacorriente As New cuentacorrienteTableAdapter
            tacuentacorriente.Connection = dbConn
            tacuentacorriente.FillByPersona(dtcuentas, id)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function


    Public Function CargarContactosById(ByVal dtcontactos As contactoDataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim tacontactos As New contactoTableAdapter
            tacontactos.Connection = dbConn
            tacontactos.FillByPersona(dtcontactos, id)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function



    Public Function CargarVistaPoliza(ByVal dtPoliza As VistaPolizasDataTable, ByVal fecha As Date) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taVistaPolizas As New VistaPolizasTableAdapter
            taVistaPolizas.Connection = dbConn
            taVistaPolizas.Fill(dtPoliza, fecha, fecha, fecha, fecha)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function
    Public Function CargarVistaPolizaFlitro(ByVal dtPoliza As VistaPolizaFiltroDataTable) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taVistaPolizas As New VistaPolizaFiltroTableAdapter
            taVistaPolizas.Connection = dbConn
            taVistaPolizas.Fill(dtPoliza)

        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function

    Public Function CargarVistaPolizaPorCliente(ByVal dtPoliza As VistaPolizasPorClienteDataTable, ByVal id As Integer) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taVistaPolizas As New VistaPolizasPorClienteTableAdapter
            taVistaPolizas.Connection = dbConn
            taVistaPolizas.Fill(dtPoliza, id, id, id, id)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function


    Public Function CargarVistaNotificaciones(ByVal dtVistaNotificaciones As VistaNotificacionesDataTable, ByVal fecha As Date) As String
        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Try
            'Se abre la conexion
            dbConn.Open()
            Dim taVistaNotificaciones As New VistaNotificacionesTableAdapter
            taVistaNotificaciones.Connection = dbConn
            taVistaNotificaciones.Fill(dtVistaNotificaciones, fecha)
        Catch ex As OleDbException
            sError = ex.ToString
        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try
        Return sError
    End Function

    'Public Function SQL_BuscarAsignatura(ByVal dtAsignatura As asignaturasDataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taAsignatura As New asignaturasTableAdapter
    '        taAsignatura.Connection = dbConn
    '        taAsignatura.BuscarAsignatura(dtAsignatura, id)
    '    Catch ex As MySqlException
    '        sError = ex.ToString
    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try
    '    Return sError
    'End Function

    'Public Function SQL_BuscarArea(ByVal dtArea As areasDataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taArea As New areasTableAdapter
    '        taArea.Connection = dbConn
    '        taArea.BuscarArea(dtArea, id)
    '    Catch ex As MySqlException
    '        sError = ex.ToString
    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try
    '    Return sError
    'End Function



    'Public Function SQL_BuscarModalidad(ByVal dtModalidad As modalidadesDataTable, ByVal id As Integer) As String
    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()
    '        Dim taModalidad As New modalidadesTableAdapter
    '        taModalidad.Connection = dbConn
    '        taModalidad.BuscarModalidad(dtModalidad, id)
    '    Catch ex As MySqlException
    '        sError = ex.ToString
    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try
    '    Return sError
    'End Function





    'Public Function SQL_BuscarHora(ByVal dtHora As horasDataTable, ByVal hIni As TimeSpan, ByVal hFin As TimeSpan, ByVal sDias As String) As String

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taHora As New horasTableAdapter

    '        taHora.Connection = dbConn

    '        taHora.BuscarHora(dtHora, hIni, hFin, sDias)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try


    '    Return sError
    'End Function


    'Public Function SQL_BuscarDocencia(ByVal dtDocencia As docenciaDataTable, ByVal iId As Integer) As String

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taDocencia As New docenciaTableAdapter

    '        taDocencia.Connection = dbConn

    '        taDocencia.BuscarDocencia(dtDocencia, iId)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try


    '    Return sError
    'End Function


    'Public Function SQL_BuscarAlumno(ByVal dtCliente As alumnosDataTable, ByVal iId As Integer) As String

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taAlumno As New alumnosTableAdapter

    '        taAlumno.Connection = dbConn

    '        taAlumno.BuscarAlumno(dtCliente, iId)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try


    '    Return sError
    'End Function


    'Public Function SQL_BuscarProfesor(ByVal dtCliente As profesoresDataTable, ByVal iId As Integer) As String

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taProfesor As New profesoresTableAdapter

    '        taProfesor.Connection = dbConn

    '        taProfesor.BuscarProfesor(dtCliente, iId)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try


    '    Return sError
    'End Function


    'Public Function SQL_MaxAlumnosAula(ByVal Hini As TimeSpan, ByVal Hfin As TimeSpan, ByVal idAula As Integer) As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMax As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taMatricula As New matriculasTableAdapter
    '        Dim sHini, sHfin As String
    '        sHfin = ""
    '        sHfin &= Hfin.Hours.ToString() & ":" & Hfin.Minutes.ToString()

    '        sHini = ""
    '        sHini &= Hini.Hours.ToString() & ":" & Hini.Minutes.ToString()
    '        taMatricula.Connection = dbConn
    '        iMax = taMatricula.MaxAula(sHini, sHfin, idAula)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMax

    'End Function


    Public Function SQL_MaxCodContacto() As Integer

        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Dim iMaxContacto As Integer = -1
        Try
            'Se abre la conexion
            dbConn.Open()

            Dim taContacto As New contactoTableAdapter

            taContacto.Connection = dbConn
            If taContacto.MaxId IsNot Nothing Then
                iMaxContacto = taContacto.MaxId
            Else
                iMaxContacto = 0
            End If


        Catch ex As Exception

            sError = ex.ToString

        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try

        Return iMaxContacto

    End Function
    Public Function SQL_MaxCodPersona() As Integer

        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)
        Dim iMaxPersona As Integer = -1
        Try
            'Se abre la conexion
            dbConn.Open()

            Dim taPersona As New personasTableAdapter

            taPersona.Connection = dbConn
            If taPersona.MaxId IsNot Nothing Then
                iMaxPersona = taPersona.MaxId
            Else
                iMaxPersona = 0
            End If


        Catch ex As Exception

            sError = ex.ToString

        Finally
            'Cierro la conexión si llegó a abrirse
            If dbConn.State = ConnectionState.Open Then
                dbConn.Close()
            End If
        End Try

        Return iMaxPersona

    End Function
    'Public Function SQL_MaxCodDocencia() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxDocencia As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taDocencia As New docenciaTableAdapter

    '        taDocencia.Connection = dbConn
    '        If taDocencia.maxIdDocencia IsNot Nothing Then
    '            iMaxDocencia = taDocencia.maxIdDocencia
    '        Else
    '            iMaxDocencia = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxDocencia

    'End Function



    'Public Function SQL_MaxCodHoras() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxHoras As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taHoras As New horasTableAdapter

    '        taHoras.Connection = dbConn
    '        If taHoras.maxIdHoras IsNot Nothing Then
    '            iMaxHoras = taHoras.maxIdHoras
    '        Else
    '            iMaxHoras = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxHoras

    'End Function

    'Public Function SQL_MaxCodAulas() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxAulas As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taAulas As New aulasTableAdapter

    '        taAulas.Connection = dbConn
    '        If taAulas.maxIdAula IsNot Nothing Then
    '            iMaxAulas = taAulas.maxIdAula
    '        Else
    '            iMaxAulas = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxAulas

    'End Function


    'Public Function SQL_MaxCodArea() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxareas As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taareas As New areasTableAdapter

    '        taareas.Connection = dbConn
    '        If taareas.maxIdArea IsNot Nothing Then
    '            iMaxareas = taareas.maxIdArea
    '        Else
    '            iMaxareas = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxareas

    'End Function



    'Public Function SQL_MaxCodAlumno() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxalumnos As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taalumnos As New alumnosTableAdapter

    '        taalumnos.Connection = dbConn
    '        If taalumnos.maxIdAlumno IsNot Nothing Then
    '            iMaxalumnos = taalumnos.maxIdAlumno
    '        Else
    '            iMaxalumnos = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxalumnos

    'End Function
    'Public Function SQL_MaxCodProfesor() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxProfesor As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taProfesor As New profesoresTableAdapter

    '        taProfesor.Connection = dbConn
    '        If taProfesor.maxIdProfesor IsNot Nothing Then
    '            iMaxProfesor = taProfesor.maxIdProfesor
    '        Else
    '            iMaxProfesor = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxProfesor

    'End Function



    'Public Function SQL_MaxCodLinea() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxLinea As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taLinea As New lineasTableAdapter

    '        taLinea.Connection = dbConn
    '        If taLinea.maxIdLinea IsNot Nothing Then
    '            iMaxLinea = taLinea.maxIdLinea
    '        Else
    '            iMaxLinea = 0
    '        End If

    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxLinea

    'End Function



    'Public Function SQL_MaxCodFactura() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxFactura As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taFactura As New facturasTableAdapter

    '        taFactura.Connection = dbConn
    '        If taFactura.maxIdFactura IsNot Nothing Then
    '            iMaxFactura = taFactura.maxIdFactura
    '        Else
    '            iMaxFactura = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxFactura

    'End Function
    'Public Function SQL_MaxCodAsignatura() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxAsignatura As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taAsignatura As New asignaturasTableAdapter

    '        taAsignatura.Connection = dbConn
    '        If taAsignatura.maxIdAsignatura IsNot Nothing Then
    '            iMaxAsignatura = taAsignatura.maxIdAsignatura
    '        Else
    '            iMaxAsignatura = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxAsignatura

    'End Function


    'Public Function SQL_MaxCodModalidad() As Integer

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim iMaxModalidad As Integer = -1
    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim taModalidad As New modalidadesTableAdapter

    '        taModalidad.Connection = dbConn
    '        If taModalidad.maxIdModalidad IsNot Nothing Then
    '            iMaxModalidad = taModalidad.maxIdModalidad
    '        Else
    '            iMaxModalidad = 0
    '        End If


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return iMaxModalidad

    'End Function


    Friend Function GuardarTabla(ByVal dtTabla As DataTable) As String

        'Variables locales
        Dim sError As String = ""
        Dim dbConn As New OleDbConnection(cadenaConexion)

        'Obtención del datatable auxiliar con los cambios a actualizar
        Dim dtAuxiliar As DataTable

        dtAuxiliar = dtTabla.GetChanges

        If dtAuxiliar IsNot Nothing Then
            Try
                dbConn.Open()

                If TypeOf dtTabla Is autoDataTable Then
                    Dim taauto As New autoTableAdapter

                    taauto.Connection = dbConn

                    taauto.Update(dtTabla)



                ElseIf TypeOf dtTabla Is hogarDataTable Then
                    Dim tahogar As New hogarTableAdapter

                    tahogar.Connection = dbConn

                    tahogar.Update(dtTabla)

                ElseIf TypeOf dtTabla Is pymecomercioDataTable Then
                    Dim tapymecomercio As New pymecomercioTableAdapter

                    tapymecomercio.Connection = dbConn

                    tapymecomercio.Update(dtTabla)

                ElseIf TypeOf dtTabla Is polizaDataTable Then
                    Dim taPoliza As New polizaTableAdapter

                    taPoliza.Connection = dbConn

                    taPoliza.Update(dtTabla)

                ElseIf TypeOf dtTabla Is notificacionesDataTable Then
                    Dim tanotificaciones As New notificacionesTableAdapter

                    tanotificaciones.Connection = dbConn

                    tanotificaciones.Update(dtTabla)

                ElseIf TypeOf dtTabla Is personasDataTable Then
                    Dim tapersonas As New personasTableAdapter

                    tapersonas.Connection = dbConn

                    tapersonas.Update(dtTabla)

                ElseIf TypeOf dtTabla Is cuentacorrienteDataTable Then
                    Dim tacuentacorriente As New cuentacorrienteTableAdapter

                    tacuentacorriente.Connection = dbConn

                    tacuentacorriente.Update(dtTabla)

                ElseIf TypeOf dtTabla Is loginDataTable Then
                    Dim talogin As New loginTableAdapter

                    talogin.Connection = dbConn

                    talogin.Update(dtTabla)


                ElseIf TypeOf dtTabla Is contactoDataTable Then
                    Dim tacontacto As New contactoTableAdapter

                    tacontacto.Connection = dbConn

                    tacontacto.Update(dtTabla)

                ElseIf TypeOf dtTabla Is relacionesDataTable Then

                    Dim tarelaciones As New relacionesTableAdapter
                    tarelaciones.Connection = dbConn
                    For Each relacionesR As relacionesRow In dtTabla.GetChanges.Rows

                        If (relacionesR.RowState = DataRowState.Added) Then
                            Select Case relacionesR.Relacion
                                Case "Marido"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Mujer")
                                Case "Mujer"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Marido")
                                Case "Padre"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Hijo")
                                Case "Hijo"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Padre")
                                Case "Tio"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Sobrino")
                                Case "Sobrino"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Tio")
                                Case "Primo"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Primo")
                                Case "Abuelo"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Nieto")
                                Case "Nieto"
                                    CType(dtTabla, relacionesDataTable).AddrelacionesRow(relacionesR.IdPersona2, relacionesR.idPersona1, "Abuelo")
                            End Select
                        End If
                    Next
                    tarelaciones.Update(dtTabla)
                End If

                'Aceptar cambios en el datatable actualizado
                dtTabla.AcceptChanges()

            Catch ex As Exception
                'Capturo el mensaje de error
                sError = ex.ToString
                'Deshago los cambios (OPCIONAL)
                dtTabla.RejectChanges()
            Finally
                'Cierro la conexión si llegó a abrirse
                If dbConn.State = ConnectionState.Open Then
                    dbConn.Close()
                End If
            End Try
        End If

        Return sError
    End Function






    'Public Function SQL_ExisteDocencia(ByVal iCod As Long) As Boolean

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim bExiste As Boolean = False

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim tadocencias As New docenciaTableAdapter

    '        tadocencias.Connection = dbConn

    '        bExiste = tadocencias.ExisteDocencia(iCod)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return bExiste
    'End Function

    'Public Function SQL_ExisteDocenciaAlum(ByVal iCod As Long) As Boolean

    '    'Variables locales
    '    Dim sError As String = ""
    '    Dim dbConn As New MySqlConnection(sCadena)
    '    Dim bExiste As Boolean = False

    '    Try
    '        'Se abre la conexion
    '        dbConn.Open()

    '        Dim tadocencias As New matriculasTableAdapter


    '        tadocencias.Connection = dbConn

    '        bExiste = tadocencias.ExisteMatricula(iCod)


    '    Catch ex As MySqlException

    '        sError = ex.ToString

    '    Finally
    '        'Cierro la conexión si llegó a abrirse
    '        If dbConn.State = ConnectionState.Open Then
    '            dbConn.Close()
    '        End If
    '    End Try

    '    Return bExiste
    'End Function







End Module
